******************************************************************************************************************************************* fragment
**************************************** AGGREGATE DATA DOFILE
******************************************************************************************************************************************* fragment
clear all
set mem 3g  
set more off
set matsize 11000
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
*********************************************
use "C:\PlanChoice\multic_pol_uic.dta" 
sum 
describe, fullnames
drop if plan_family!="EP1" & plan_family!="HMO" & plan_family!="POS" & plan_family!="PPO"
************************************************ 
* generate date variable 
gen year  = substr( year_mo ,1,4)
destring year, replace
gen month = substr( year_mo ,5,2)
destring month, replace
gen data_date  = ym(year, month )
format data_date %tm
************************************************
* CHANGE PREMIUMS FOR 2014
************************************************
 sort customer_number  polnbr data_date
merge customer_number polnbr data_date using aux_2014.dta
************************************************
* replace the old premium variable (when wrong) for the corrected for 2014
replace total_premium = premium2014fixed if _merge==3
drop noofelig _merge premium2014fixed 
************************************************
* CHANGE PREMIUMS FOR 2015
************************************************
 sort customer_number  polnbr data_date
merge customer_number polnbr data_date using aux_2015.dta
************************************************
* replace the old premium variable (when wrong) for the corrected for 2014
replace total_premium = premium2015fixed if _merge==3
drop noofelig _merge premium2015fixed 

*******************************************************
drop if month!=1
*******************************************************

************************************************

 g par2 = prime_subs/ elig

************************************************
* solving the repetition of brokers (I am not considering that some firms are in two markets)
sort customer_number minor_market polnbr data_date
order customer_number minor_market polnbr data_date
local characteristics = " par2 par in_network_deductible in_network_oopm out_of_network_oopm physician_copay coinsurance total_premium out_of_network_ind_ded"
collapse (firstnm) sic_cd plan_family rx_code prime_plan_code year month minor_market (sum)  prime_mbr prime_subs  (mean)  elig `characteristics' , by(customer_number polnbr data_date)

*************************************************
* generate the variables to regress
gen phys_copay_dif0 = 0 if physician_copay==0
replace phys_copay_dif0=1 if phys_copay_dif0==.
gen phys_copay_intensity = phys_copay_dif0*physician_copay
*******
gen outnet_oop_dif0 = 0 if out_of_network_oopm==0
replace outnet_oop_dif0=1 if outnet_oop_dif0==.
gen outnet_oop_intensity = outnet_oop_dif0*out_of_network_oopm
********
gen outnet_ded_dif0 =0 if out_of_network_ind_ded==0
replace outnet_ded_dif0=1 if outnet_ded_dif0==.
gen outnet_ded_intensity = outnet_ded_dif0*out_of_network_ind_ded
replace outnet_oop_dif0=0 if outnet_oop_dif0==1 & outnet_ded_dif0==0
*********
egen rx_networks = group(minor_market rx_code)
tab rx_networks, gen(rx_dum)
*********
egen firm= group(customer_number)
tab data_date, gen(timedum)
tab minor_market, gen(markets)
tab prime_plan_code , generate(plan_dum)
************************************************
* generate premiums under the four different methodologies (a. value for january b. mode value of suscribers c. mean value of premiums d. regression)
* drop observations with incorrect number of subscribers 
bys customer_number  data_date: egen total_subs = total(prime_subs)
bys customer_number  data_date: egen total_mbr  = total(prime_mbr)
drop if total_subs>=elig
bys customer_number  data_date: egen min_premium  = min(total_premium)
drop if min_premium<=0
bys customer_number  data_date: egen min_prime_subs= min(prime_subs)
drop if min_prime_subs<=0
drop if elig==.
* methodology a) 
gen avg_price_a = (total_premium/prime_mbr)*12
* methodology b)
local var "prime_mbr prime_subs elig total_premium"
foreach x of local var {
bys year customer_number  polnbr: egen mode_`x' = mode(`x'), maxmode
bys year customer_number  polnbr: egen max_`x' = max(`x')
replace mode_`x' = max_`x' if  mode_`x'==.
}
bys customer_number  data_date: egen mode_total_mbr  = total(mode_prime_mbr)
gen avg_price_b = (mode_total_premium/mode_total_mbr)*12
* methodology c)
bys year customer_number  polnbr: egen avg_price_c = mean(avg_price_a)
* methodology d)
bys firm year: egen premium_mean = mean(avg_price_a)
gen new_premium = avg_price_a - premium_mean
reg new_premium coinsurance in_network_deductible in_network_oopm phys_copay* outnet_oop* outnet_ded_intensity rx_dum*
predict predicted_price, xb
gen avg_price_d = predicted_price + premium_mean


***********************************************
* keep only month equals to January
drop if month!=1
gen aux=1
bys customer_number data_date: egen num_plansbyfirm = count(aux)
drop aux
drop timedum*
tab data_date, gen(timedum)
***********************************************
* generate shares
gen mkt_share = prime_subs/elig
bys customer_number  data_date: egen total_mkt_share = total(mkt_share)
gen out_option = 1 - total_mkt_share 
gen log_inside_share = log(mkt_share/total_mkt_share)
gen y = log(mkt_share/out_option)
***********************************************
bys firm data_date: egen NestedLogitIV_11 = mean(avg_price_a)
bys firm data_date: egen NestedLogitIV_12 = mean(avg_price_b)
bys firm data_date: egen NestedLogitIV_13 = mean(avg_price_c)
bys firm data_date: egen NestedLogitIV_14 = mean(avg_price_d)
bys firm data_date: egen NestedLogitIV_2 = mean(coinsurance)
bys firm data_date: egen NestedLogitIV_3 = mean(in_network_oopm) 
bys firm data_date: egen NestedLogitIV_4 = mean(in_network_deductible) 
bys firm data_date: egen NestedLogitIV_5 = mean(outnet_ded_intensity) 
bys firm data_date: egen NestedLogitIV_6 = mean(phys_copay_dif0) 
bys firm data_date: egen NestedLogitIV_7 = mean(phys_copay_intensity) 
bys firm data_date: egen NestedLogitIV_8 = mean(outnet_oop_dif0) 
bys firm data_date: egen NestedLogitIV_9 = mean(outnet_oop_intensity) 
bys firm data_date: egen NestedLogitIV_10 = mean(outnet_ded_intensity) 
***********************************************
*drop if avg_annual_premium==.
***********************************************
** DEMEAN LEFT HAND SIDE VARIABLE 
***********************************************
bys firm: egen ymean = mean(y)
gen new_y = y - ymean
***********************************************
***********************************************
*create variables diferent by categories of plan_family
gen POS = 1 if plan_family=="POS"
replace POS=0 if POS==.
gen PPO = 1 if plan_family=="PPO"
replace PPO=0 if PPO==.
gen HMO = 1 if plan_family=="HMO"
replace HMO=0 if HMO==.
gen EP = 1 if plan_family=="EP1"
replace EP=0 if EP==.
***********************************************
saveold data_auxiliar_pricefixed.dta, replace
***********************************************
set more off
local char1 = " avg_price_a coinsurance "
reg new_y `char1' timedum* , vce(cluster firm)
gen sample = 1 if e(sample)==1
drop if sample!=1
keep customer_number minor_market polnbr data_date par par2 avg_price_a avg_price_b avg_price_c avg_price_d coinsurance ///
in_network_deductible in_network_oopm phys_copay* outnet_oop* outnet_ded_intensity rx_dum* ///
NestedLogitIV_11 NestedLogitIV_12 NestedLogitIV_13 NestedLogitIV_14 NestedLogitIV_2 ///
NestedLogitIV_3 NestedLogitIV_4 NestedLogitIV_5 NestedLogitIV_6 NestedLogitIV_7 NestedLogitIV_8 ///
NestedLogitIV_9 NestedLogitIV_10 prime_plan_code total_premium sic_cd num_plansbyfirm
rename data_date date_month 
saveold plan_characteristics.dta, replace 
gen aux = 1 
collapse (mean) aux num_plansbyfirm ,by(customer_number polnbr date_month)
drop aux
sort polnbr date_month
**** CLEAN REPETITION FOR SAME FIRMS IN THE SAME TIME 
saveold firm_dictionary.dta, replace
***********************************************
***********************************************
